package com.swn.common.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.LinkedCaseInsensitiveMap;
/**
 * 功能：加载当前数据库用户下表参数工具类
 * 说明：DbTableUtil.map中，key是表名，value是Map<String, Object>。
 * 其中的key代表参数名，value是对应的单表操作的sql或集合：
 * COLNAMES（字段名列表）、PREPARED_PKWHERE（预编译主键查询条件语句 ）、PREPARED_SELECTSQL（默认查询语句）、
 * PREPARED_GETSQL（预编译获取单条数据语句）、PREPARED_REMOVESQL（预编译删除单条数据语句）、COLCODELIST（获取字段列）
 * 注意：
 *  1.在监听ContextLoaderListenerImpl的contextInitialized启动；
 *  2.在DbHeper内调用
 *  3.DbTableUtil替换了该文件。从连接池获取连接经过测试几乎不消耗性能
 * 
 * @author taozhaocai
 * @Date 2016-5-6  上午10:27:35
 */
public class DbJdbcTableUtil {
	private static Logger logger = Logger.getLogger("DbJdbcTableUtil.class");
	/** 保存表相关信息的map ***/
	public static Map<String, Map<String, Object>> map = new HashMap<String, Map<String, Object>>();
	
	/**
	 * 服务器启动时监听调用的方法：初始化oracle当前用户表参数入口方法
	 *  void
	 *
	 * @Author taozhaocai
	 * @Date 2016-5-6 上午10:29:29 
	 */
	 public static void initOracleTable(){
		 //清空map,防止多次被调用
		 map.clear();
		 
		 JdbcTemplate  jdbcTemplate = null;
		 Connection con = null;
		 Statement st = null;
		 ResultSet rs = null;
		 logger.info("开始初始化表信息......");
		 long t1 = System.currentTimeMillis();
		 //oracle所有表名称sql
		 String thisUserTableSql = "select TABLE_NAME from tabs order by TABLE_NAME";
		 try {
			//从容器中获取jdbcTemplate
			jdbcTemplate	= (JdbcTemplate)ServiceLocator.getBean("jdbcTemplate");
			con = jdbcTemplate.getDataSource().getConnection();
			
			st = con.createStatement();
			rs = st.executeQuery(thisUserTableSql);
			while(rs.next()){
				String tableCode = rs.getString("TABLE_NAME");
				 setTableMap(tableCode,con);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{

			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(st!=null){
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(con!=null){
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		
		}
		 
		 long t2 = System.currentTimeMillis();
		 logger.info("初始化表信息完毕，用时秒："+(t2-t1)/1000.00);
	 }
	 
	 
	 public static void setTableMap(String tableCode,Connection con){
		 //String colNames = "select wmsys.wm_concat(column_name) from user_col_comments where table_name=upper('"+tableCode+"')";
		 String keyCol = "select col.column_name from user_constraints con,  user_cons_columns col where con.constraint_name = col.constraint_name and con.constraint_type='P' and col.table_name = '"+tableCode+"'";
		 List<LinkedCaseInsensitiveMap<String>> keyColMapList = getkeyColMapList(tableCode,con);
		 /** 所操纵数据库表名称 */
		 String TABLE_NAME = tableCode;
		 /** 字段名列表 */
		 //String COLNAMES = jdbcTemplate.queryForObject(colNames, String.class);
		 String COLNAMES = wmConcat(tableCode,con);
		 /** 预编译主键查询条件语句 */
		 String PREPARED_PKWHERE = concatkeyColName(keyColMapList);
		 /** 默认查询语句 */
		 String PREPARED_SELECTSQL = "SELECT " + COLNAMES + " FROM " + TABLE_NAME;
		 /** 预编译获取单条数据语句 */
		 String PREPARED_GETSQL = PREPARED_SELECTSQL + " WHERE " + PREPARED_PKWHERE;
		 /** 预编译删除单条数据语句 */
		 String PREPARED_REMOVESQL = "DELETE FROM " + TABLE_NAME + " WHERE " + PREPARED_PKWHERE;
		 /** 获取字段列 */
		 List<String> COLCODELIST = java.util.Arrays.asList(COLNAMES.split(",")); 
		 Map<String, Object> paramMap = new HashMap<String, Object>();
		 paramMap.put("COLNAMES", COLNAMES);
		 paramMap.put("PREPARED_PKWHERE", PREPARED_PKWHERE);
		 paramMap.put("PREPARED_SELECTSQL", PREPARED_SELECTSQL);
		 paramMap.put("PREPARED_GETSQL", PREPARED_GETSQL);
		 paramMap.put("PREPARED_REMOVESQL", PREPARED_REMOVESQL);
		 paramMap.put("COLCODELIST", COLCODELIST);
		 map.put(TABLE_NAME, paramMap);
	 }
	 
	 private static List<LinkedCaseInsensitiveMap<String>> getkeyColMapList(String tableCode,Connection con){
		 List<LinkedCaseInsensitiveMap<String>>  mapList = new ArrayList<LinkedCaseInsensitiveMap<String>>();
		 Statement st = null;
		 ResultSet rs = null;
		 
		 String keyColSql = "select col.column_name from user_constraints con,  user_cons_columns col where con.constraint_name = col.constraint_name and con.constraint_type='P' and col.table_name = '"+tableCode+"'";
		 
		try {
			 st = con.createStatement();
			 rs = st.executeQuery(keyColSql);
			 LinkedCaseInsensitiveMap<String> linkedCaseInsensitiveMap = null;
			 while(rs.next()){
				 linkedCaseInsensitiveMap = new LinkedCaseInsensitiveMap<String>();
				 linkedCaseInsensitiveMap.put("column_name", rs.getString("column_name"));
				 mapList.add(linkedCaseInsensitiveMap);
			 }
			 
		} catch (Exception e) {
			logger.error(e);
		}finally{
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(st!=null){
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		 
			return mapList;
	 }
	 
	 
	 public static String wmConcat(String tableCode,Connection con){
		 String retrunValue = "";
		 StringBuffer colNames = new StringBuffer("");
		 StringBuffer sql = new StringBuffer();
		 sql.append("select column_name from user_col_comments where table_name=upper('");
		 sql.append(tableCode).append("')");
		 Statement st = null;
		 ResultSet rs = null;
		 try {
			 st = con.createStatement();
			 rs = st.executeQuery(sql.toString());
			 while(rs.next()){
				 colNames.append(rs.getString("column_name")).append(",");
			 }
			 //去掉逗号
			 retrunValue = colNames.substring(0, colNames.length()-1);
		} catch (Exception e) {
			logger.error(e);
		}finally{
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(st!=null){
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		 
		 return retrunValue;
	 }
	  
	 
	 /**
	  * 
	  * 获取某个表的列代码，用逗号间隔
	  * @param tableName
	  * @return String
	  *
	  * @Author taozhaocai
	  * @Date 2016-5-6 上午10:31:09 
	  */
	 public static String COLNAMES(String tableName){
		 if(tableName!=null&&!"".equals(tableName)){
			return  (String)(map.get(tableName.toUpperCase())).get("COLNAMES");
		 }
		 return null;
	 }
	 
	 /**
	  * 获取"预编译主键查询条件语句"
	  * 
	  * @param tableName
	  * @return String
	  *
	  * @Author taozhaocai
	  * @Date 2016-5-6 上午10:34:38 
	  */
	 public static String PREPARED_PKWHERE(String tableName){
		 if(tableName!=null&&!"".equals(tableName)){
			return  (String)(map.get(tableName.toUpperCase())).get("PREPARED_PKWHERE");
		 }
		 return null;
	 }
	 
	 /**
	  * 获取默认查询语句
	  * 
	  * @param tableName
	  * @return String
	  *
	  * @Author taozhaocai
	  * @Date 2016-5-6 上午11:01:05 
	  */
	 public static String PREPARED_SELECTSQL(String tableName){
		 if(tableName!=null&&!"".equals(tableName)){
			return  (String)(map.get(tableName.toUpperCase())).get("PREPARED_SELECTSQL");
		 }
		 return null;
	 }
	 
	 /**
	  * 获取预编译获取单条数据语句 
	  * 
	  * @param table
	  * @return String
	  *
	  * @Author Administrator
	  * @Date 2016-5-6 上午11:22:10 
	  */
	 public static String PREPARED_GETSQL(String table){
		 if(table!=null&&!"".equals(table)){
			return  (String)(map.get(table.toUpperCase())).get("PREPARED_GETSQL");
		 }
		 return null;
	 }
	 
	 /**
	  * 获取“预编译删除单条数据语句 ”
	  * 
	  * @param tableName
	  * @return String
	  *
	  * @Author Administrator
	  * @Date 2016-5-6 上午11:22:54 
	  */
	 public static String PREPARED_REMOVESQL(String tableName){
		 if(tableName!=null&&!"".equals(tableName)){
			return  (String)(map.get(tableName.toUpperCase())).get("PREPARED_REMOVESQL");
		 }
		 return null;
	 }
	 
	 /**
	  * 获取“获取字段列”
	  * 
	  * @param tableName
	  * @return List<String>
	  *
	  * @Author Administrator
	  * @Date 2016-5-6 上午11:23:35 
	  */
	 public static List<String> COLCODELIST(String tableName){
		 if(tableName!=null&&!"".equals(tableName)){
			return  (List<String>)(map.get(tableName.toUpperCase())).get("COLCODELIST");
		 }
		 return null;
	 }
	 
	 
	 
	 /**
	  * 拼装主键具名参数字符串
	  * 
	  * @param keyColMapList
	  * @return String
	  *
	  * @Author taozhaocai
	  */
	 private static String concatkeyColName(List<LinkedCaseInsensitiveMap<String>> keyColMapList){
		 StringBuffer concatkeyCol = new StringBuffer();
		 for (int i = 0; i < keyColMapList.size(); i++) {
			 LinkedCaseInsensitiveMap<String> map = keyColMapList.get(i);
			 String key = (String)map.get("column_name");
			 if(i==keyColMapList.size()-1){
				 concatkeyCol.append(key).append("=:").append(key);
			 }else{
				 concatkeyCol.append(key).append("=:").append(key).append(" and ");
			 }
		}
		 return concatkeyCol.toString();
	 }

	 
	
}
